In [1]:
! pip install plotly==5.6.0
! pip install "notebook>=5.3" "ipywidgets>=7.5"

Requirement already satisfied: plotly==5.6.0 in /Users/Momo/opt/anaconda3/lib/python3.9/site-packages (5.6.0)
Requirement already satisfied: six in /Users/Momo/opt/anaconda3/lib/python3.9/site-packages (from plotly==5.6.0) (1.16.0)
Requirement already satisfied: tenacity>=6.2.0 in /Users/Momo/opt/anaconda3/lib/python3.9/site-packages (from plotly==5.6.0) (8.0.1)
Requirement already satisfied: notebook>=5.3 in /Users/Momo/opt/anaconda3/lib/python3.9/site-packages (6.4.5)
Requirement already satisfied: ipywidgets>=7.5 in /Users/Momo/opt/anaconda3/lib/python3.9/site-packages (7.6.5)
Requirement already satisfied: prometheus-client in /Users/Momo/opt/anaconda3/lib/python3.9/site-packages (from notebook>=5.3) (0.11.0)
Requirement already satisfied: pyzmq>=17 in /Users/Momo/opt/anaconda3/lib/python3.9/site-packages (from notebook>=5.3) (22.2.1)
Requirement already satisfied: nbformat in /Users/Momo/opt/anaconda3/lib/python3.9/site-packages (from notebook>=5.3) (5.1.3)
Requirement already satisfied: terminado>=0.8.3 in /Users/Momo/opt/anaconda3/lib/python3.9/site-packages (from notebook>=5.3) (0.9.4)
Requirement already satisfied: jupyter-client>=5.3.4 in /Users/Momo/opt/anaconda3/lib/python3.9/site-packages (from notebook>=5.3) (6.1.12)
Requirement already satisfied: ipython-genutils in /Users/Momo/opt/anaconda3/lib/python3.9/site-packages (from notebook>=5.3) (0.2.0)
Requirement already satisfied: jupyter-core>=4.6.1 in /Users/Momo/opt/anaconda3/lib/python3.9/site-packages (from notebook>=5.3) (4.8.1)
Requirement already satisfied: argon2-cffi in /Users/Momo/opt/anaconda3/lib/python3.9/site-packages (from notebook>=5.3) (20.1.0)
Requirement already satisfied: Send2Trash>=1.5.0 in /Users/Momo/opt/anaconda3/lib/python3.9/site-packages (from notebook>=5.3) (1.8.0)
Requirement already satisfied: ipykernel in /Users/Momo/opt/anaconda3/lib/python3.9/site-packages (from notebook>=5.3) (6.4.1)
Requirement already satisfied: nbconvert in /Users/Momo/opt/anaconda3/lib/python3.9/site-packages (from notebook>=5.3) (6.1.0)
Requirement already satisfied: jinja2 in /Users/Momo/opt/anaconda3/lib/python3.9/site-packages (from notebook>=5.3) (2.11.3)
Requirement already satisfied: traitlets>=4.2.1 in /Users/Momo/opt/anaconda3/lib/python3.9/site-packages (from notebook>=5.3) (5.1.0)
Requirement already satisfied: tornado>=6.1 in /Users/Momo/opt/anaconda3/lib/python3.9/site-packages (from notebook>=5.3) (6.1)
Requirement already satisfied: widgetsnbextension~=3.5.0 in /Users/Momo/opt/anaconda3/lib/python3.9/site-packages (from ipywidgets>=7.5) (3.5.1)
Requirement already satisfied: jupyterlab-widgets>=1.0.0 in /Users/Momo/opt/anaconda3/lib/python3.9/site-packages (from ipywidgets>=7.5) (1.0.0)
Requirement already satisfied: ipython>=4.0.0 in /Users/Momo/opt/anaconda3/lib/python3.9/site-packages (from ipywidgets>=7.5) (7.29.0)
Requirement already satisfied: debugpy<2.0,>=1.0.0 in /Users/Momo/opt/anaconda3/lib/python3.9/site-packages (from ipykernel->notebook>=5.3) (1.4.1)
Requirement already satisfied: appnope in /Users/Momo/opt/anaconda3/lib/python3.9/site-packages (from ipykernel->notebook>=5.3) (0.1.2)
Requirement already satisfied: matplotlib-inline<0.2.0,>=0.1.0 in /Users/Momo/opt/anaconda3/lib/python3.9/site-packages (from ipykernel->notebook>=5.3) (0.1.2)
Requirement already satisfied: prompt-toolkit!=3.0.0,!=3.0.1,<3.1.0,>=2.0.0 in /Users/Momo/opt/anaconda3/lib/python3.9/site-packages (from ipython>=4.0.0->ipywidgets>=7.5) (3.0.20)
Requirement already satisfied: pexpect>4.3 in /Users/Momo/opt/anaconda3/lib/python3.9/site-packages (from ipython>=4.0.0->ipywidgets>=7.5) (4.8.0)
Requirement already satisfied: setuptools>=18.5 in /Users/Momo/opt/anaconda3/lib/python3.9/site-packages (from ipython>=4.0.0->ipywidgets>=7.5) (58.0.4)
Requirement already satisfied: backcall in /Users/Momo/opt/anaconda3/lib/python3.9/site-packages (from ipython>=4.0.0->ipywidgets>=7.5) (0.2.0)
Requirement already satisfied: pickleshare in /Users/Momo/opt/anaconda3/lib/python3.9/site-packages (from ipython>=4.0.0->ipywidgets>=7.5) (0.7.5)
Requirement already satisfied: jedi>=0.16 in /Users/Momo/opt/anaconda3/lib/python3.9/site-packages (from ipython>=4.0.0->ipywidgets>=7.5) (0.18.0)
Requirement already satisfied: pygments in /Users/Momo/opt/anaconda3/lib/python3.9/site-packages (from ipython>=4.0.0->ipywidgets>=7.5) (2.10.0)
Requirement already satisfied: decorator in /Users/Momo/opt/anaconda3/lib/python3.9/site-packages (from ipython>=4.0.0->ipywidgets>=7.5) (5.1.0)
Requirement already satisfied: parso<0.9.0,>=0.8.0 in /Users/Momo/opt/anaconda3/lib/python3.9/site-packages (from jedi>=0.16->ipython>=4.0.0->ipywidgets>=7.5) (0.8.2)
Requirement already satisfied: python-dateutil>=2.1 in /Users/Momo/opt/anaconda3/lib/python3.9/site-packages (from jupyter-client>=5.3.4->notebook>=5.3) (2.8.2)
Requirement already satisfied: jsonschema!=2.5.0,>=2.4 in /Users/Momo/opt/anaconda3/lib/python3.9/site-packages (from nbformat->notebook>=5.3) (3.2.0)
Requirement already satisfied: six>=1.11.0 in /Users/Momo/opt/anaconda3/lib/python3.9/site-packages (from jsonschema!=2.5.0,>=2.4->nbformat->notebook>=5.3) (1.16.0)
Requirement already satisfied: attrs>=17.4.0 in /Users/Momo/opt/anaconda3/lib/python3.9/site-packages (from jsonschema!=2.5.0,>=2.4->nbformat->notebook>=5.3) (21.2.0)
Requirement already satisfied: pyrsistent>=0.14.0 in /Users/Momo/opt/anaconda3/lib/python3.9/site-packages (from jsonschema!=2.5.0,>=2.4->nbformat->notebook>=5.3) (0.18.0)
Requirement already satisfied: ptyprocess>=0.5 in /Users/Momo/opt/anaconda3/lib/python3.9/site-packages (from pexpect>4.3->ipython>=4.0.0->ipywidgets>=7.5) (0.7.0)
Requirement already satisfied: wcwidth in /Users/Momo/opt/anaconda3/lib/python3.9/site-packages (from prompt-toolkit!=3.0.0,!=3.0.1,<3.1.0,>=2.0.0->ipython>=4.0.0->ipywidgets>=7.5) (0.2.5)
Requirement already satisfied: cffi>=1.0.0 in /Users/Momo/opt/anaconda3/lib/python3.9/site-packages (from argon2-cffi->notebook>=5.3) (1.14.6)
Requirement already satisfied: pycparser in /Users/Momo/opt/anaconda3/lib/python3.9/site-packages (from cffi>=1.0.0->argon2-cffi->notebook>=5.3) (2.20)
Requirement already satisfied: MarkupSafe>=0.23 in /Users/Momo/opt/anaconda3/lib/python3.9/site-packages (from jinja2->notebook>=5.3) (1.1.1)
Requirement already satisfied: pandocfilters>=1.4.1 in /Users/Momo/opt/anaconda3/lib/python3.9/site-packages (from nbconvert->notebook>=5.3) (1.4.3)
Requirement already satisfied: bleach in /Users/Momo/opt/anaconda3/lib/python3.9/site-packages (from nbconvert->notebook>=5.3) (4.0.0)
Requirement already satisfied: testpath in /Users/Momo/opt/anaconda3/lib/python3.9/site-packages (from nbconvert->notebook>=5.3) (0.5.0)
Requirement already satisfied: nbclient<0.6.0,>=0.5.0 in /Users/Momo/opt/anaconda3/lib/python3.9/site-packages (from nbconvert->notebook>=5.3) (0.5.3)
Requirement already satisfied: defusedxml in /Users/Momo/opt/anaconda3/lib/python3.9/site-packages (from nbconvert->notebook>=5.3) (0.7.1)
Requirement already satisfied: mistune<2,>=0.8.1 in /Users/Momo/opt/anaconda3/lib/python3.9/site-packages (from nbconvert->notebook>=5.3) (0.8.4)
Requirement already satisfied: entrypoints>=0.2.2 in /Users/Momo/opt/anaconda3/lib/python3.9/site-packages (from nbconvert->notebook>=5.3) (0.3)
Requirement already satisfied: jupyterlab-pygments in /Users/Momo/opt/anaconda3/lib/python3.9/site-packages (from nbconvert->notebook>=5.3) (0.1.2)
Requirement already satisfied: async-generator in /Users/Momo/opt/anaconda3/lib/python3.9/site-packages (from nbclient<0.6.0,>=0.5.0->nbconvert->notebook>=5.3) (1.10)
Requirement already satisfied: nest-asyncio in /Users/Momo/opt/anaconda3/lib/python3.9/site-packages (from nbclient<0.6.0,>=0.5.0->nbconvert->notebook>=5.3) (1.5.1)
Requirement already satisfied: webencodings in /Users/Momo/opt/anaconda3/lib/python3.9/site-packages (from bleach->nbconvert->notebook>=5.3) (0.5.1)
Requirement already satisfied: packaging in /Users/Momo/opt/anaconda3/lib/python3.9/site-packages (from bleach->nbconvert->notebook>=5.3) (21.0)
Requirement already satisfied: pyparsing>=2.0.2 in /Users/Momo/opt/anaconda3/lib/python3.9/site-packages (from packaging->bleach->nbconvert->notebook>=5.3) (3.0.4)
In [2]:
import numpy as np
import pandas as pd
import matplotlib as mpl
import matplotlib.pyplot as plt
from plotly import graph_objs as go
In [3]:
! wget https://raw.githubusercontent.com/opencasestudies/ocs-bp-opioid-rural-urban/master/data/simpler_import/county_pop_arcos.csv -P ./data
! wget https://raw.githubusercontent.com/opencasestudies/ocs-bp-opioid-rural-urban/master/data/simpler_import/land_area.csv -P ./data
! wget https://raw.githubusercontent.com/opencasestudies/ocs-bp-opioid-rural-urban/master/data/simpler_import/county_annual.csv -P ./data

--2022-02-18 11:54:58--  https://raw.githubusercontent.com/opencasestudies/ocs-bp-opioid-rural-urban/master/data/simpler_import/county_pop_arcos.csv
Resolving raw.githubusercontent.com (raw.githubusercontent.com)... 2606:50c0:8001::154, 2606:50c0:8002::154, 2606:50c0:8003::154, ...
Connecting to raw.githubusercontent.com (raw.githubusercontent.com)|2606:50c0:8001::154|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 2771268 (2.6M) [text/plain]
Saving to: ‘./data/county_pop_arcos.csv’

county_pop_arcos.cs 100%[===================>]   2.64M  17.0MB/s    in 0.2s    

2022-02-18 11:54:59 (17.0 MB/s) - ‘./data/county_pop_arcos.csv’ saved [2771268/2771268]

--2022-02-18 11:54:59--  https://raw.githubusercontent.com/opencasestudies/ocs-bp-opioid-rural-urban/master/data/simpler_import/land_area.csv
Resolving raw.githubusercontent.com (raw.githubusercontent.com)... 2606:50c0:8001::154, 2606:50c0:8002::154, 2606:50c0:8003::154, ...
Connecting to raw.githubusercontent.com (raw.githubusercontent.com)|2606:50c0:8001::154|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 672411 (657K) [text/plain]
Saving to: ‘./data/land_area.csv’

land_area.csv       100%[===================>] 656.65K  --.-KB/s    in 0.09s   

2022-02-18 11:54:59 (6.91 MB/s) - ‘./data/land_area.csv’ saved [672411/672411]

--2022-02-18 11:54:59--  https://raw.githubusercontent.com/opencasestudies/ocs-bp-opioid-rural-urban/master/data/simpler_import/county_annual.csv
Resolving raw.githubusercontent.com (raw.githubusercontent.com)... 2606:50c0:8001::154, 2606:50c0:8002::154, 2606:50c0:8003::154, ...
Connecting to raw.githubusercontent.com (raw.githubusercontent.com)|2606:50c0:8001::154|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 1331693 (1.3M) [text/plain]
Saving to: ‘./data/county_annual.csv’

county_annual.csv   100%[===================>]   1.27M  --.-KB/s    in 0.1s    

2022-02-18 11:55:00 (11.5 MB/s) - ‘./data/county_annual.csv’ saved [1331693/1331693]
In [6]:
import sqlite3 as sq3

con = sq3.connect("../ds4ph_hw4/opioid.db")

population_d = pd.read_sql_query("SELECT * from population", con)
annual_d = pd.read_sql_query("SELECT * from annual", con)
land_d = pd.read_sql_query("SELECT * from land", con)

con.close
Out[6]:
<function Connection.close>
In [7]:
annual_d.loc[(annual_d.BUYER_STATE == "AR") & (annual_d.BUYER_COUNTY == "MONTGOMERY"), "countyfips"] = 5097
annual_d.loc[(annual_d.BUYER_STATE == "AR") & (annual_d.BUYER_COUNTY == "MONTGOMERY")].head()
Out[7]:
BUYER_COUNTY BUYER_STATE year count DOSAGE_UNIT countyfips
17429 17430 MONTGOMERY AR 2006 469 175390 5097
17430 17431 MONTGOMERY AR 2007 597 241270 5097
17431 17432 MONTGOMERY AR 2008 561 251760 5097
17432 17433 MONTGOMERY AR 2009 554 244160 5097
17433 17434 MONTGOMERY AR 2010 449 247990 5097
In [8]:
annual_d = annual_d[annual_d.countyfips != "NA"]
annual_d.tail(10)
Out[8]:
BUYER_COUNTY BUYER_STATE year count DOSAGE_UNIT countyfips
27731 27732 ZAPATA TX 2014 446 150400 48505
27732 27733 ZAVALA TX 2006 270 148460 48507
27733 27734 ZAVALA TX 2007 365 164740 48507
27734 27735 ZAVALA TX 2008 268 191700 48507
27735 27736 ZAVALA TX 2009 240 193530 48507
27736 27737 ZAVALA TX 2010 248 200100 48507
27737 27738 ZAVALA TX 2011 406 244800 48507
27738 27739 ZAVALA TX 2012 473 263700 48507
27739 27740 ZAVALA TX 2013 399 186700 48507
27740 27741 ZAVALA TX 2014 162 148930 48507
In [9]:
annual_d = annual_d.astype({"DOSAGE_UNIT" : "float"})
annual_d = annual_d.assign(dosage_in_milli = annual_d.DOSAGE_UNIT / 1000000)
ave_annual = annual_d.groupby(["BUYER_STATE", "year"]).agg(ave_dosage = ("dosage_in_milli", "mean")).reset_index()
ave_annual.head()
Out[9]:
BUYER_STATE year ave_dosage
0 AK 2006 0.783350
1 AK 2007 0.909075
2 AK 2008 1.100459
3 AK 2009 1.120053
4 AK 2010 1.167372
In [10]:
import plotly.express as px

fig = px.scatter(x=ave_annual.year, y=ave_annual.ave_dosage, 
                 color=ave_annual.BUYER_STATE)

fig.show()